Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 003 / db3tips3.arc / DB3MISC.TXT < prev next >

Wrap

Text File | 1986-12-21 | 17.9 KB | 478 lines

Preformatted dBASE SUM Command (PC Magazine Vol 5 No 15 Sept 16, 1986 Power User) The dBASE SUM command reports totals of numeric fields, but its output is Spartan at best -- just the raw numbers and their field names. If you need a presentable record of the state of your data file on a given date, it just won't do. Here's a useful idea for transforming dBASE's Report Form capability into a a much-enhanced SUM command. Like SUM, it rapidly totals a file; unlike SUM, however, the totals are clearly, consistently formatted with headings and date. You don't even have to retype the long SUM command next time. You start by making a copy of any .FRM file you've written previously. Next, issue the MODIFY REPORT command and, at the second screen, specify " " as the Subtotal Field. Then answer Y to the inquiry about wanting a Summary Report. Finally, go back and remove the Subtotal Field, then save the .FRM file to disk and run it. When you run a report modified in this way, only the headings and totals print out, saving a lot of time and paper. Editor's Note: This tip cons dBASE into buying Screen 2 of the Report Form. It hustles dBASE into accepting nothing (" ") as a Subtotal Field. (You have to make some entry there or dBASE won't give you the chance to specify Summary Report.) If you just save the report with " " as the Subtotal Field, however, dBASE will judge that every record fits within the first subtotal group. You'll get the right totals, but they'll be reported twice -- first as subtotals, then as totals. However, by then modifying the Report Form by removing the " ", you further trick dBASE into suppressing the "**SUBTOTAL" line entirely. After that, whenever you want global totals from your file, just type REPO FORM filename. You could use this same .FRM to total segments of a file by adding a SET FILTER TO or a FOR condition. Or you could make a permanent disk record of this nicely formatted data by adding TO FILE filename to the REPORT command. ----------------------------------------------------------------- 1-2-3 to dBASE III (PC Magazine Vol 5 No 15 Sept 16, 1986 Power User) An alternative procedure for converting 123 worksheets to dBASE III is to create a 123 print file whose range includes only the data (not the headings). Set the left margin to 0 and the right to 240. Select Options Other Unformatted, then Go. For best results, reset any numeric formatting options in the spreadsheet first. Your dBASE file structure must be set up with the same field widths as the 123 columns. The dBASE import command is: APPEND FROM filename.PRN SDF Editor's Note: The file produced by 123 is in pure ASCII format with each spreadsheet row delimited by a carriage return-line feed. Most word processors and many other applications can read this format as well. If you find it useful to export data often, set up a 123 macro. Create and name the data cell range. Then execute: \RFR rangename~ \RR rangename~ \PF filename.PRN~ \OOUML0~MR240~MT0~MB0~QAGQ Exporting from dBASE back to 123 is as easy as typing: COPY TO filename.TXT DELIMITED DELIMITED causes dBASE to format the output file with each field separated by a comma and character fields enclosed in double quotes. Once back in 123, position the cursor where you want the data to drop in and type: \FIN filename.TXT 123 will note the commas and create the column structure accordingly, converting the quoted strings into labels. Unfortunately, something is lost in the translation -- your formulas. What you see is what you get. Another potential problem is 123's unfortunate 240-character width limit for importing and exporting. If these limits don't get in the way, however, you can switch a file back and forth between 123 and dBASE, taking advantage of each program's special strengths. One of the advantages of the new dBASE III Plus is that it will allow you to APPEND 1-2-3 .WKS files directly. ----------------------------------------------------------------- Printing a Field on Multiple Lines The following dBASE III routine can be used to print a field on multiple lines. Each field that is passed to the program will be split into substrings of the specified length or shorter. Words will not be split. The substrings will be printed starting at the row/column coordinates. Syntzx to call the program is: DO WRAP WITH fieldname, length, row, col * Program: WRAP.PRG parameters string, length, row, col strlen = LEN(string) DO WHILE strlen > length spot = length * Do not divide a word DO WHILE SUBSTR (string, spot, 1) # " " spot = spot - 1 ENDDO @ row, col SAY SUBSTR(string, 1, spot - 1) row = row + 1 string = SUBSTR(string, spot+1, strlen-spot) strlen = LEN(string) ENDDO @ row, col SAY string RETURN ----------------------------------------------------------------- Safety First (PC Magazine Vol 5 No 16 Sept 30, 1986 Power User) Before upgrading to dBASE III Plus, you should first install the program in a separate subdirectory so you can test whether your existing Version 1.1 programs will still run. If you want to run Plus with your old programs, one immediaet change you'll want to make is to remove the STATUS=ON and HELP=ON lines from Plus's standard CONFIG.DB file. By installing Plus in this tentative way, you can switch back to the older, tested version simply by loading it from its own directory. Later, when you're absolutely convinced, you can deinstall both versions and put Plus on the normal directory from which you've loaded dBASE in the past. Editor's Note: There is far less difference between dBASE III Plus and dBASE III than between III and II. In fact, there is very little in the new version that will cause problems with your older programs, but there are some gremlins. For example, SET COLOR TO commands now use letters instead of numbers. Any applications that use screen enhancements such as underline or reverse video for clarity must be revised; and, once revised, they won't run properly in the older dBASE. Plus takes more than twice as much disk space -- up from 149K to 312K. The old standard workstation -- a dual floppy system with dBASE and applications programs on drive A: and data on drive B: -- is strictly a thing of the past. dBASE III Plus takes more operating memory, too. If you have only 256K, you're up a creek; while Ashton- Tate supplies a paddle in the form of a bare-bones CONFIG.DB, memory is strictly rationed and there's little available for such memory- intensive functions as indexing and sorting. If you couldn't work a day without resident programs like ProKey or SideKick, you're out of work until you add more RAM. There are lots of little things to complain about. The report and label generators are simply terrible; while adding no new capabilities, they give you much less information and require many more steps to complete the same work. In the interactive mode, the cursor jumps to line 24 each time the dot prompt appears. This makes it difficult or impossible to leave information on the screen while you ask questions, make replacements, etc. ----------------------------------------------------------------- Eyes Left (PC Magazine Vol 5 No 20 Nov 25, 1986 Power User) dBASE's TRIM() function is perfect for removing trailing blanks, but neither dBASE II nor Version 1 of dBASE III offers the reverse: an easy way to trim leading blanks. LTRIM.PRG performs this function, assuming you're working with a character field. LTRIM.PRG: CLEAR SET TALK OFF ACCE "LTRIM which FILE?" TO fil ACCE "REPL which FIELD?" TO fld USE &fil DO WHIL .NOT. EOF() ref=1 DO WHIL ref<LEN(&fld) ref2=SUBS(&fld,ref,1) IF ref2#' ' EXIT ELSE ref=ref+1 ENDIF ENDDO REPL &fld with SUBS(&fld,ref) ? &fld SKIP ENDDO SET TALK ON Editor's Note: dBASE III Plus solves this sometimes annoying problem by adding a direct function, LTRIM():REPL <filename) WITH LTRIM(<filename>). This new LTRIM() applies only to character-type fields. If you have an older version of dBASE, however, LTRIM.PRG will come in handy if, for example, you want to clean up a name field that has leading blanks, or if you need to index a file on the ZIP code and you know that some ZIPs have not been entered flush-left. The general technique -- indexing right from the leftmost character -- can be useful in different ways. Suppose you want to express a number flush-left, and it's inconvenient or impossible to use dBASE III's PICTURE "@B" function. You could modify the structure of the numeric field, converting it to character format. At this point, all the numeric data in your new character field is flush- right. The LTRIM.PRG will eat the leading blanks, pulling the character string over to the leftmost position. With a few changes you can even LTRIM on the fly, without modifying the data structure from numeric to character and without replacing the contents. If you fieldname for a numeric field 8 digits wide is called "Numbr", the following routine will create a left- ustrified (and TRIMed) character string, no matter what value is in the Numbr field: fld=STR(Numbr,8) ref=1 DO WHIL fld=' ' .AND. ref<8 fld=SUBS(fld,2) ref=ref+1 ENDDO fld=TRIM(fld) If you're LTRIMing a character field on the fly, only the first and third lines differ: fld=chrfield ref=1 DO WHIL fld=' ' .AND. ref<LEN(chrfield) fld=SUBS(fld,2) ref=ref+1 ENDDO fld=TRIM(fld) If you're using dBASE II, remember that the syntax to STORe memory variables is different. The last line above would read: STOR TRIM(fld) TO fld ----------------------------------------------------------------- High-Power Pause (PC Magazine Vol 5 No 20 Nov 25, 1986 Power User) The procedure below gives you an easy way to display messages from with a dBASE application. For example, if a menu offers choices of 1-9 and the user enters "k", the program would trap this impossible response as follows: DO WHILE .T. * insert you menu screen here * GET user's choice and READ IF .NOT. response $ "123456789" DO pause WITH [No choice "]+response+["] ELSE EXIT ENDIF error-trap ENDDO menu If the user's response is not allowable, the DO WHILE loop stays active, reprinting the menu screen and reGETting the user's response. Thus, if the user gets past your error trap, you know it's safe to process his response. Editor's Note: Although the PAUSE.PRG below was designed for noncritical error messages, if you pause long enough to think it through, you'll probably find a lot of other uses for this handy dBASE III procedure. As examples, you might DO pause WITH any of the following: "Don't forget to backup this new data." "Remember to invoice this extra item." "Bob wants THREE copies of this report." "REM each record MUST have a zip before indexing." "Please verify coding on West Coast items." "Verify: this check amount is over $1,000." PAUSE.PRG thus has a lot of horsepower under the hood for such a short procedure -- some of these might be useful in other programs you write. For example, by passing the message as a parameter, you can send the user a context-sensitive message. It surrounds your message with arrows and a space. It centers the message: the expression, (80-LEN (mess))/2, asks the computer to pace off half the distance from column 39 depending on the LENgth of the current message. (Note: maximum message length is 76 characters.) This same line also enhances the message: unless you have SET INTENSITY OFF, the GET appears in reverse video. Of course, you're not actually GETting anything, so the CLEAR GETS straightens that out. PAUSE displays on the same screen line every time, so the user becomes accustomed to receiving minor error messages there. (You could choose a different line just by changing the @24s.) It beeps to alert the user that an unusual condition exists. Finally, having delivered its message, PAUSE gets out of the way without requiring a keystroke. It tidies up by erasing its message and RETUrns to the calling program. You might even ask the user at the beginning of a session "How long do you want reminders displayed?" and STORE that number to a variable called "duration." Then change the DO WHILEx<25 loop to DO WHILE x<duration. * PAUSE.PRG PARA mess ? CHR(7) SET CONS OFF mess=CHR(26)+" "+mess+" "+CHR(27) @ 24,0 @ 24,(80-LEN(mess))/2 GET mess CLEA GETS x=0 DO WHIL x<25 x=x+1 ENDD x=message duration @ 24,0 SET CONS ON ----------------------------------------------------------------- dBASE Subdirectories (PC World Star-Dot-Star November 1986) Subdirectories make organizing your hard disk a breeze because you can put related data and programs into logical places. dBASE III Plus lacks a simple command to change the current directory. It's possible to use the command RUN CD path to accommodate this end, but that requires additional memory, plus extra time to load the DOS command interpreter. Fortunately, dBASE III Plus can easily load and execute assembly language programs. CD.PRG and CD.BIN use this feature to change the current directory. CD.PRG closes any open data files, loads the assembly language program CD.BIN into memory, transfers control to CD.BIN, then eliminates CD.BIN from memory and returns control to the calling program. Use DEBUG to create CD.BIN as shown below. Store both CD.PRG and CD.BIN in the same subdirectory that contains dBASE III Plus. To use the programs, simply issue the command DO CD WITH path, replacing path with the appropriate subdirectory specification. * CD.PRG parameter dir close databases load cd call cd with dir ? 'Directory now changed to ',dir release cd return CD.BIN: A>DEBUG -A 100 XXXX:0100 MOV DX,BX XXXX:0102 MOV AX,3B00 XXXX:0105 INT 21 XXXX:0107 RETF XXXX:0108 -N CD.BIN CX 0000 :8 -W -Q ----------------------------------------------------------------- dBASE Cross Tabulation (PC Magazine Vol 5 No 22 Dec 23, 1986 Power User) Occasionally, you need COUNTs for all possible combinations of certain fields in dBASE files. Multiway tabulation (e.g., "How are people in a database distributed by sex and age?") is a fairly common tool in statistics, and it is sometimes seen in other contexts. To do this with a series of COUNTs or TOTALs, however, requires that the whole sequence of instructions be assembled from scratch for each specific query. XTAB.PRG offers a more general solution, though it can process only one table at a time. To use the program, issue the command: DO XTAB The procedure GETs the data filename, the number of variables (i.e., fields or expressions) to be tabulated, and the name of each one. Then it creates a summary database to store the resulting table (ending in .DBX) and begins processing. Editor's Note: Life would be simpler if dBASE would allow you to TOTAL ON more than one field. Then, to your original file you could just add a numeric field caller counter (a length of 4 would usually be sufficient), REPL ALL counter WITH 1,INDEX ON key expression, and TOTAL ON key expression. ** XTAB.PRG SET TALK OFF CLEAR nvar=0 origfile=SPAC(20) @ 3,0 SAY "CROSS TAB PROGRAM" @ 5,0 SAY "Which file? " GET origfile READ origfile=TRIM(origfile) @ 7,0 SAY "How many variables?" GET nvar READ SELE 1 USE &origfile ALIA origfile keyexp="" * keyexp = list of vars delimited with plus signs i=1 DO WHILE i<=nvar .AND. LEN(keyexp)<250 nomvar=SPAC(25) @ 8+i,0 SAY "Variable "+STR(i,2)+" field/expression? " GET nomvar READ nomvar=TRIM(nomvar) IF TYPE([&nomvar})='N' * Note: change below if decimals keyexp=keyexp+'STR('+nomvar+',10,0)' ELSE keyexp=keyexp+nomvar ENDIF IF i<nvar keyexp=keyexp+"+[ ]+" ENDIF i=i+1 ENDDO * create summary file SET SAFE OFF COPY STRU EXTENDED TO tempsum SELE 2 USE tempsum DELE FOR RECNO()>2 PACK GOTO 1 REPL field_name WITH "TOT",field_type WITH "N",; field_len WITH 5,field_dec WITH 0 sumname=TRIM(origfile)+".dbx" CREATE &sumname FROM tempsum USE &sumname ERAS tempsum.dbf * scanning the data file ? "Standby ....processing" SELE 1 INDEX ON &keyexp TO temp SET INDEX TO temp SET SAFE ON keyref=&keyexp DO WHILE .NOT. EOF() COUN WHILE &keyexp=keyref TO ncount SELE 2 APPEND BLANK REPL tot WITH ncount,key WITH keyref SKIP SELE 1 keyref=&keyexp ENDDO CLEAR SELE 2 DISP OFF ALL tot,key CLOSE DATA * Optional: ERAS temp.ndx SET TALK ON RETU ----------------------------------------------------------------- Flexible Signal (PC Magazine Vol 5 No 22 Dec 23, 1986 Power User) When running a long dBASE process, you want to know when it's through so you can do something else in the meantime. The short SIGNAL.PRG is loud enough to be heard from the next room. Some users might like to add a WAIT statement at the end. * SIGNAL.PRG ? CHR(7)+TIME() ?? CHR(7)+" - Process Complete." ?? CHR(7) ?? CHR(7) ?? CHR(7) ?? CHR(7) ?? CHR(7) ?? CHR(7) Editor's Note: If signaling is useful in your dBASE programs, you may want to develop this theme. For example, by combining The Norton Utilities with dBASE's RUN capability, you could signal a major error with an SOS in Morse code thus: RUN BEEP /f4000 /r3 /d1 RUN BEEP /f4000 /r3 /d3 RUN BEEP /f4000 /r3 /d1 A musically minded user may want to announce that he's ready to start his process with: RUN BEEP /f1000 /r3 /d1 RUN BEEP /f800 /d8